# pythonProject5/models/db.py

# 正确导入 Base
from db.session import Base, engine

from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, Boolean, Index
from sqlalchemy.orm import relationship
from datetime import datetime, timezone
from core.config import settings
from core.logger import logger
# 用户模型
class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, index=True, nullable=False)
    email = Column(String(100), unique=True, index=True, nullable=False)
    salt = Column(String(64), nullable=False)  # 新增：密码盐值
    hashed_password = Column(String(255), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    is_active = Column(Boolean, default=True)

    # 关联记录（双向关系）
    records = relationship("TranslationRecord", back_populates="user", cascade="all, delete-orphan")
    asr_records = relationship("ASRRecord", back_populates="user", cascade="all, delete-orphan")

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "username": self.username,
            "email": self.email,
            "created_at": self.created_at,
            "updated_at": self.updated_at,
            "is_active": self.is_active
        }


# 翻译记录模型
class TranslationRecord(Base):
    __tablename__ = "translation_records"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    source_audio_path = Column(String(255), index=True)
    source_text = Column(Text, nullable=False)
    target_text = Column(Text, nullable=False)
    source_language = Column(String(10), default=settings.DEFAULT_SOURCE_LANG)
    target_language = Column(String(10), nullable=False)
    duration_seconds = Column(Integer, default=0)
    model_used = Column(String(100), default="whisper-small")
    translation_engine = Column(String(50), default="nllb")
    created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
    updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))

    # 关联用户
    user = relationship("User", back_populates="records")

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "user_id": self.user_id,
            "source_audio_path": self.source_audio_path,
            "source_text": self.source_text,
            "target_text": self.target_text,
            "source_language": self.source_language,
            "target_language": self.target_language,
            "duration_seconds": self.duration_seconds,
            "model_used": self.model_used,
            "translation_engine": self.translation_engine,
            "created_at": self.created_at,
            "updated_at": self.updated_at
        }


# =======================
# 新增：ASR 记录模型
# =======================
class ASRRecord(Base):
    __tablename__ = "asr_records"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    audio_path = Column(String(255), nullable=False)        # 音频文件存储路径
    filename = Column(String(255), nullable=False)          # 原始文件名
    text = Column(Text, nullable=False)                     # 识别出的文本
    language = Column(String(10), nullable=True)            # 语言代码，如 'zh', 'en'
    duration_seconds = Column(Integer, default=0)           # 音频时长（秒）
    file_size = Column(Integer, nullable=True)              # 文件大小（字节）
    model_used = Column(String(100), default="whisper-small")  # 使用的 ASR 模型
    created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
    updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))

    # 关联用户
    user = relationship("User", back_populates="asr_records")

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "user_id": self.user_id,
            "audio_path": self.audio_path,
            "filename": self.filename,
            "text": self.text,
            "language": self.language,
            "duration_seconds": self.duration_seconds,
            "file_size": self.file_size,
            "model_used": self.model_used,
            "created_at": self.created_at,
            "updated_at": self.updated_at
        }


# =======================
# 创建索引
# =======================
Index("idx_user_username", User.username)
Index("idx_record_user_id", TranslationRecord.user_id)
Index("idx_record_created_at", TranslationRecord.created_at)

# 为 ASR 记录添加索引
Index("idx_asr_user_id", ASRRecord.user_id)
Index("idx_asr_created_at", ASRRecord.created_at)
Index("idx_asr_filename", ASRRecord.filename)


# =======================
# 数据库初始化函数
# =======================
def init_db():
    """创建所有表"""
    try:
        Base.metadata.create_all(bind=engine)
        logger.info("数据库表创建成功：users, translation_records, asr_records")
    except Exception as e:
        logger.error(f"数据库初始化失败: {e}")
        raise